<!DOCTYPE html>
<html lang="zh-CN">
    <head>
        <meta charset="utf-8">
        <meta name="viewport" content="width=device-width, initial-scale=1">
        <meta name="robots" content="noodp" />
        <title>二、可重复读隔离级别完全解决幻读了吗 - L_B__</title><meta name="referrer" content="no-referrer">
<meta name="description" content="二、可重复读隔离级别完全解决幻读了吗"><meta property="og:title" content="二、可重复读隔离级别完全解决幻读了吗" />
<meta property="og:description" content="二、可重复读隔离级别完全解决幻读了吗" />
<meta property="og:type" content="article" />
<meta property="og:url" content="https://acking-you.github.io/posts/%E4%BA%8C%E5%8F%AF%E9%87%8D%E5%A4%8D%E8%AF%BB%E9%9A%94%E7%A6%BB%E7%BA%A7%E5%88%AB%E5%AE%8C%E5%85%A8%E8%A7%A3%E5%86%B3%E5%B9%BB%E8%AF%BB%E4%BA%86%E5%90%97/" /><meta property="og:image" content="https://acking-you.github.io/logo.png"/><meta property="article:section" content="posts" />
<meta property="article:published_time" content="2023-05-28T00:00:00+00:00" />
<meta property="article:modified_time" content="2023-05-28T00:00:00+00:00" />

<meta name="twitter:card" content="summary_large_image"/>
<meta name="twitter:image" content="https://acking-you.github.io/logo.png"/>

<meta name="twitter:title" content="二、可重复读隔离级别完全解决幻读了吗"/>
<meta name="twitter:description" content="二、可重复读隔离级别完全解决幻读了吗"/>
<meta name="application-name" content="FeelIt">
<meta name="apple-mobile-web-app-title" content="FeelIt"><meta name="theme-color" content="#ffffff"><meta name="msapplication-TileColor" content="#da532c"><link rel="canonical" href="https://acking-you.github.io/posts/%E4%BA%8C%E5%8F%AF%E9%87%8D%E5%A4%8D%E8%AF%BB%E9%9A%94%E7%A6%BB%E7%BA%A7%E5%88%AB%E5%AE%8C%E5%85%A8%E8%A7%A3%E5%86%B3%E5%B9%BB%E8%AF%BB%E4%BA%86%E5%90%97/" /><link rel="prev" href="https://acking-you.github.io/posts/%E5%B8%83%E9%9A%86%E8%BF%87%E6%BB%A4%E5%99%A8/" /><link rel="next" href="https://acking-you.github.io/posts/%E4%B8%80%E5%88%9D%E8%A7%81%E7%B4%A2%E5%BC%95/" /><link rel="stylesheet" href="/css/page.min.css"><link rel="stylesheet" href="/css/home.min.css"><script type="application/ld+json">
    {
        "@context": "http://schema.org",
        "@type": "BlogPosting",
        "headline": "二、可重复读隔离级别完全解决幻读了吗",
        "inLanguage": "zh-CN",
        "mainEntityOfPage": {
            "@type": "WebPage",
            "@id": "https:\/\/acking-you.github.io\/posts\/%E4%BA%8C%E5%8F%AF%E9%87%8D%E5%A4%8D%E8%AF%BB%E9%9A%94%E7%A6%BB%E7%BA%A7%E5%88%AB%E5%AE%8C%E5%85%A8%E8%A7%A3%E5%86%B3%E5%B9%BB%E8%AF%BB%E4%BA%86%E5%90%97\/"
        },"genre": "posts","keywords": "二、可重复读隔离级别完全解决幻读了吗","wordcount":  3285 ,
        "url": "https:\/\/acking-you.github.io\/posts\/%E4%BA%8C%E5%8F%AF%E9%87%8D%E5%A4%8D%E8%AF%BB%E9%9A%94%E7%A6%BB%E7%BA%A7%E5%88%AB%E5%AE%8C%E5%85%A8%E8%A7%A3%E5%86%B3%E5%B9%BB%E8%AF%BB%E4%BA%86%E5%90%97\/","datePublished": "2023-05-28T00:00:00+00:00","dateModified": "2023-05-28T00:00:00+00:00","publisher": {
            "@type": "Organization",
            "name": "作者"},"author": {
                "@type": "Person",
                "name": "作者"
            },"description": "二、可重复读隔离级别完全解决幻读了吗"
    }
    </script></head><body data-header-desktop="auto" data-header-mobile="auto"><script>(window.localStorage && localStorage.getItem('theme') ? localStorage.getItem('theme') === 'dark' : ('auto' === 'auto' ? window.matchMedia('(prefers-color-scheme: dark)').matches : 'auto' === 'dark')) && document.body.setAttribute('theme', 'dark');</script>

        <div id="mask"></div><div class="wrapper"><header class="desktop" id="header-desktop">
    <div class="header-wrapper">
        <div class="header-title">
            <a href="/" title="L_B__">L_B__</a>
        </div>
        <div class="menu">
            <div class="menu-inner"><a class="menu-item" href="/posts/"> 文章 </a><a class="menu-item" href="/tags/"> 标签 </a><a class="menu-item" href="/categories/"> 分类 </a><span class="menu-item delimiter"></span><span class="menu-item search" id="search-desktop">
                        <input type="text" placeholder="搜索文章标题或内容..." id="search-input-desktop">
                        <a href="#" class="search-button search-toggle" id="search-toggle-desktop" title="搜索">
                            <i class="fas fa-search fa-fw"></i>
                        </a>
                        <a href="#" class="search-button search-clear" id="search-clear-desktop" title="清空">
                            <i class="fas fa-times-circle fa-fw"></i>
                        </a>
                        <span class="search-button search-loading" id="search-loading-desktop">
                            <i class="fas fa-spinner fa-fw fa-spin"></i>
                        </span>
                    </span><a href="javascript:void(0);" class="menu-item theme-switch" title="切换主题">
                    <i class="fas fa-adjust fa-fw"></i>
                </a>
            </div>
        </div>
    </div>
</header><header class="mobile" id="header-mobile">
    <div class="header-container">
        <div class="header-wrapper">
            <div class="header-title">
                <a href="/" title="L_B__">L_B__</a>
            </div>
            <div class="menu-toggle" id="menu-toggle-mobile">
                <span></span><span></span><span></span>
            </div>
        </div>
        <div class="menu" id="menu-mobile"><div class="search-wrapper">
                    <div class="search mobile" id="search-mobile">
                        <input type="text" placeholder="搜索文章标题或内容..." id="search-input-mobile">
                        <a href="#" class="search-button search-toggle" id="search-toggle-mobile" title="搜索">
                            <i class="fas fa-search fa-fw"></i>
                        </a>
                        <a href="#" class="search-button search-clear" id="search-clear-mobile" title="清空">
                            <i class="fas fa-times-circle fa-fw"></i>
                        </a>
                        <span class="search-button search-loading" id="search-loading-mobile">
                            <i class="fas fa-spinner fa-fw fa-spin"></i>
                        </span>
                    </div>
                    <a href="#" class="search-cancel" id="search-cancel-mobile">
                        取消
                    </a>
                </div><a class="menu-item" href="/posts/" title="">文章</a><a class="menu-item" href="/tags/" title="">标签</a><a class="menu-item" href="/categories/" title="">分类</a><div class="menu-item"><a href="javascript:void(0);" class="theme-switch" title="切换主题">
                    <i class="fas fa-adjust fa-fw"></i>
                </a>
            </div></div>
    </div>
</header><div class="search-dropdown desktop">
    <div id="search-dropdown-desktop"></div>
</div>
<div class="search-dropdown mobile">
    <div id="search-dropdown-mobile"></div>
</div><main class="main">
                <div class="container"><div class="toc" id="toc-auto">
            <h2 class="toc-title">目录</h2>
            <div class="toc-content" id="toc-content-auto"></div>
        </div><article class="page single" data-toc="disable"><div class="featured-image"><img
        class="lazyload"
        src="/svg/loading.min.svg"
        data-src="https://img-blog.csdnimg.cn/img_convert/7213ded01d686aab797968b2a6064358.png#pic_center"
        data-srcset="https://img-blog.csdnimg.cn/img_convert/7213ded01d686aab797968b2a6064358.png#pic_center, https://img-blog.csdnimg.cn/img_convert/7213ded01d686aab797968b2a6064358.png#pic_center 1.5x, https://img-blog.csdnimg.cn/img_convert/7213ded01d686aab797968b2a6064358.png#pic_center 2x"
        data-sizes="auto"
        alt="https://img-blog.csdnimg.cn/img_convert/7213ded01d686aab797968b2a6064358.png#pic_center"
        title="二、可重复读隔离级别完全解决幻读了吗" /></div><div class="single-card" data-image="true"><h2 class="single-title animated flipInX">二、可重复读隔离级别完全解决幻读了吗</h2><div class="post-meta">
                <div class="post-meta-line"><span class="post-author"><a href="/" title="Author" rel=" author" class="author"><i class="fas fa-user-circle fa-fw"></i>作者</a></span>&nbsp;<span class="post-category">出版于  <a href="/categories/mysql%E4%BA%8B%E5%8A%A1%E7%AF%87/"><i class="far fa-folder fa-fw"></i>MySQL事务篇</a></span></div>
                <div class="post-meta-line"><span><i class="far fa-calendar-alt fa-fw"></i>&nbsp;<time datetime="2023-05-28">2023-05-28</time></span>&nbsp;<span><i class="fas fa-pencil-alt fa-fw"></i>&nbsp;约 3285 字</span>&nbsp;
                    <span><i class="far fa-clock fa-fw"></i>&nbsp;预计阅读 7 分钟</span>&nbsp;</div>
            </div>
            
            <hr><div class="details toc" id="toc-static"  data-kept="">
                    <div class="details-summary toc-title">
                        <span>目录</span>
                        <span><i class="details-icon fas fa-angle-right"></i></span>
                    </div>
                    <div class="details-content toc-content" id="toc-content-static"><nav id="TableOfContents">
  <ul>
    <li><a href="#httpsxiaolincodingcommysqltransactionphantomhtml什么是幻读什么是幻读"><a href="https://xiaolincoding.com/mysql/transaction/phantom.html#%E4%BB%80%E4%B9%88%E6%98%AF%E5%B9%BB%E8%AF%BB">#</a>什么是幻读？</a></li>
    <li><a href="#httpsxiaolincodingcommysqltransactionphantomhtml快照读是如何避免幻读的快照读是如何避免幻读的"><a href="https://xiaolincoding.com/mysql/transaction/phantom.html#%E5%BF%AB%E7%85%A7%E8%AF%BB%E6%98%AF%E5%A6%82%E4%BD%95%E9%81%BF%E5%85%8D%E5%B9%BB%E8%AF%BB%E7%9A%84">#</a>快照读是如何避免幻读的？</a></li>
    <li><a href="#httpsxiaolincodingcommysqltransactionphantomhtml当前读是如何避免幻读的当前读是如何避免幻读的"><a href="https://xiaolincoding.com/mysql/transaction/phantom.html#%E5%BD%93%E5%89%8D%E8%AF%BB%E6%98%AF%E5%A6%82%E4%BD%95%E9%81%BF%E5%85%8D%E5%B9%BB%E8%AF%BB%E7%9A%84">#</a>当前读是如何避免幻读的？</a></li>
    <li><a href="#httpsxiaolincodingcommysqltransactionphantomhtml幻读被完全解决了吗幻读被完全解决了吗"><a href="https://xiaolincoding.com/mysql/transaction/phantom.html#%E5%B9%BB%E8%AF%BB%E8%A2%AB%E5%AE%8C%E5%85%A8%E8%A7%A3%E5%86%B3%E4%BA%86%E5%90%97">#</a>幻读被完全解决了吗？</a>
      <ul>
        <li><a href="#httpsxiaolincodingcommysqltransactionphantomhtml第一个发生幻读现象的场景第一个发生幻读现象的场景"><a href="https://xiaolincoding.com/mysql/transaction/phantom.html#%E7%AC%AC%E4%B8%80%E4%B8%AA%E5%8F%91%E7%94%9F%E5%B9%BB%E8%AF%BB%E7%8E%B0%E8%B1%A1%E7%9A%84%E5%9C%BA%E6%99%AF">#</a>第一个发生幻读现象的场景</a></li>
        <li><a href="#httpsxiaolincodingcommysqltransactionphantomhtml第二个发生幻读现象的场景第二个发生幻读现象的场景"><a href="https://xiaolincoding.com/mysql/transaction/phantom.html#%E7%AC%AC%E4%BA%8C%E4%B8%AA%E5%8F%91%E7%94%9F%E5%B9%BB%E8%AF%BB%E7%8E%B0%E8%B1%A1%E7%9A%84%E5%9C%BA%E6%99%AF">#</a>第二个发生幻读现象的场景</a></li>
      </ul>
    </li>
    <li><a href="#httpsxiaolincodingcommysqltransactionphantomhtml总结总结"><a href="https://xiaolincoding.com/mysql/transaction/phantom.html#%E6%80%BB%E7%BB%93">#</a>总结</a></li>
  </ul>
</nav></div>
                </div><div class="content" id="content"><h1 id="mysql-可重复读隔离级别完全解决幻读了吗">MySQL 可重复读隔离级别，完全解决幻读了吗？</h1>
<p>在上一篇文章中提到，MySQL InnoDB 引擎的默认隔离级别虽然是「可重复读」，但是它很大程度上避免幻读现象（并不是完全解决了），解决的方案有两种：</p>
<ul>
<li>针对<strong>快照读</strong>（普通 select 语句），是<strong>通过 MVCC 方式解决了幻读</strong>，因为可重复读隔离级别下，事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的，即使中途有其他事务插入了一条数据，是查询不出来这条数据的，所以就很好了避免幻读问题。</li>
<li>针对<strong>当前读</strong>（select &hellip; for update 等语句），是<strong>通过 next-key lock（记录锁+间隙锁）方式解决了幻读</strong>，因为当执行 select &hellip; for update 语句的时候，会加上 next-key lock，如果有其他事务在 next-key lock 锁范围内插入了一条记录，那么这个插入语句就会被阻塞，无法成功插入，所以就很好了避免幻读问题。</li>
</ul>
<p>这两个解决方案是很大程度上解决了幻读现象，但是还是有个别的情况造成的幻读现象是无法解决的。</p>
<p>这次，就跟大家好好聊这个问题。</p>
<h2 id="httpsxiaolincodingcommysqltransactionphantomhtml什么是幻读什么是幻读"><a href="https://xiaolincoding.com/mysql/transaction/phantom.html#%e4%bb%80%e4%b9%88%e6%98%af%e5%b9%bb%e8%af%bb" target="_blank" rel="noopener noreffer">#</a>什么是幻读？</h2>
<p>首先来看看 MySQL 文档是怎么定义幻读（Phantom Read）的:</p>
<p><em><strong>The so-called phantom problem occurs within a transaction when the same query produces different sets of rows at different times. For example, if a SELECT is executed twice, but returns a row the second time that was not returned the first time, the row is a “phantom” row.</strong></em></p>
<p>翻译：当同一个查询在不同的时间产生不同的结果集时，事务中就会出现所谓的幻象问题。例如，如果 SELECT 执行了两次，但第二次返回了第一次没有返回的行，则该行是“幻像”行。</p>
<p>举个例子，假设一个事务在 T1 时刻和 T2 时刻分别执行了下面查询语句，途中没有执行其他任何语句：</p>
<div class="highlight"><pre tabindex="0" class="chroma"><code class="language-sql" data-lang="sql"><span class="k">SELECT</span><span class="w"> </span><span class="o">*</span><span class="w"> </span><span class="k">FROM</span><span class="w"> </span><span class="n">t_test</span><span class="w"> </span><span class="k">WHERE</span><span class="w"> </span><span class="n">id</span><span class="w"> </span><span class="o">&gt;</span><span class="w"> </span><span class="mi">100</span><span class="p">;</span><span class="w">
</span></code></pre></div><p>只要 T1 和 T2 时刻执行产生的结果集是不相同的，那就发生了幻读的问题，比如：</p>
<ul>
<li>T1 时间执行的结果是有 5 条行记录，而 T2 时间执行的结果是有 6 条行记录，那就发生了幻读的问题。</li>
<li>T1 时间执行的结果是有 5 条行记录，而 T2 时间执行的结果是有 4 条行记录，也是发生了幻读的问题。</li>
</ul>
<h2 id="httpsxiaolincodingcommysqltransactionphantomhtml快照读是如何避免幻读的快照读是如何避免幻读的"><a href="https://xiaolincoding.com/mysql/transaction/phantom.html#%e5%bf%ab%e7%85%a7%e8%af%bb%e6%98%af%e5%a6%82%e4%bd%95%e9%81%bf%e5%85%8d%e5%b9%bb%e8%af%bb%e7%9a%84" target="_blank" rel="noopener noreffer">#</a>快照读是如何避免幻读的？</h2>
<p>可重复读隔离级是由 MVCC（多版本并发控制）实现的，实现的方式是开始事务后（执行 begin 语句后），在执行第一个查询语句后，会创建一个 Read View，<strong>后续的查询语句利用这个 Read View，通过这个 Read View 就可以在 undo log 版本链找到事务开始时的数据，所以事务过程中每次查询的数据都是一样的</strong>，即使中途有其他事务插入了新纪录，是查询不出</p>
<p>来这条数据的，所以就很好了避免幻读问题。</p>
<p>做个实验，数据库表 t_stu 如下，其中 id 为主键。</p>
<p><img
        class="lazyload"
        src="/svg/loading.min.svg"
        data-src="https://cdn.xiaolincoding.com//mysql/other/7f9df142b3594daeaaca495abb7133f5.png"
        data-srcset="https://cdn.xiaolincoding.com//mysql/other/7f9df142b3594daeaaca495abb7133f5.png, https://cdn.xiaolincoding.com//mysql/other/7f9df142b3594daeaaca495abb7133f5.png 1.5x, https://cdn.xiaolincoding.com//mysql/other/7f9df142b3594daeaaca495abb7133f5.png 2x"
        data-sizes="auto"
        alt="https://cdn.xiaolincoding.com//mysql/other/7f9df142b3594daeaaca495abb7133f5.png"
        title="img" /></p>
<p>然后在可重复读隔离级别下，有两个事务的执行顺序如下：</p>
<p><img
        class="lazyload"
        src="/svg/loading.min.svg"
        data-src="https://cdn.xiaolincoding.com//mysql/other/e576e047dccc47d5a59636ea342750b8.png"
        data-srcset="https://cdn.xiaolincoding.com//mysql/other/e576e047dccc47d5a59636ea342750b8.png, https://cdn.xiaolincoding.com//mysql/other/e576e047dccc47d5a59636ea342750b8.png 1.5x, https://cdn.xiaolincoding.com//mysql/other/e576e047dccc47d5a59636ea342750b8.png 2x"
        data-sizes="auto"
        alt="https://cdn.xiaolincoding.com//mysql/other/e576e047dccc47d5a59636ea342750b8.png"
        title="在这里插入图片描述" /></p>
<p>从这个实验结果可以看到，即使事务 B 中途插入了一条记录，事务 A 前后两次查询的结果集都是一样的，并没有出现所谓的幻读现象。</p>
<h2 id="httpsxiaolincodingcommysqltransactionphantomhtml当前读是如何避免幻读的当前读是如何避免幻读的"><a href="https://xiaolincoding.com/mysql/transaction/phantom.html#%e5%bd%93%e5%89%8d%e8%af%bb%e6%98%af%e5%a6%82%e4%bd%95%e9%81%bf%e5%85%8d%e5%b9%bb%e8%af%bb%e7%9a%84" target="_blank" rel="noopener noreffer">#</a>当前读是如何避免幻读的？</h2>
<p>MySQL 里除了普通查询是快照读，其他都是<strong>当前读</strong>，比如 update、insert、delete，这些语句执行前都会查询最新版本的数据，然后再做进一步的操作。</p>
<p>这很好理解，假设你要 update 一个记录，另一个事务已经 delete 这条记录并且提交事务了，这样不是会产生冲突吗，所以 update 的时候肯定要知道最新的数据。</p>
<p>另外，<code>select ... for update</code> 这种查询语句是当前读，每次执行的时候都是读取最新的数据。</p>
<p>接下来，我们假设<code>select ... for update</code>当前读是不会加锁的（实际上是会加锁的），在做一遍实验。</p>
<p><img
        class="lazyload"
        src="/svg/loading.min.svg"
        data-src="https://cdn.xiaolincoding.com//mysql/other/1f872ff92b644b5f81cee2dd9188b199.png"
        data-srcset="https://cdn.xiaolincoding.com//mysql/other/1f872ff92b644b5f81cee2dd9188b199.png, https://cdn.xiaolincoding.com//mysql/other/1f872ff92b644b5f81cee2dd9188b199.png 1.5x, https://cdn.xiaolincoding.com//mysql/other/1f872ff92b644b5f81cee2dd9188b199.png 2x"
        data-sizes="auto"
        alt="https://cdn.xiaolincoding.com//mysql/other/1f872ff92b644b5f81cee2dd9188b199.png"
        title="img" /></p>
<p>这时候，事务 B 插入的记录，就会被事务 A 的第二条查询语句查询到（因为是当前读），这样就会出现前后两次查询的结果集合不一样，这就出现了幻读。</p>
<p>所以，<strong>Innodb 引擎为了解决「可重复读」隔离级别使用「当前读」而造成的幻读问题，就引出了间隙锁</strong>。</p>
<p>假设，表中有一个范围 id 为（3，5）间隙锁，那么其他事务就无法插入 id = 4 这条记录了，这样就有效的防止幻读现象的发生。</p>
<p><img
        class="lazyload"
        src="/svg/loading.min.svg"
        data-src="https://cdn.xiaolincoding.com/gh/xiaolincoder/mysql/%E9%94%81/gap%E9%94%81.drawio.png"
        data-srcset="https://cdn.xiaolincoding.com/gh/xiaolincoder/mysql/%E9%94%81/gap%E9%94%81.drawio.png, https://cdn.xiaolincoding.com/gh/xiaolincoder/mysql/%E9%94%81/gap%E9%94%81.drawio.png 1.5x, https://cdn.xiaolincoding.com/gh/xiaolincoder/mysql/%E9%94%81/gap%E9%94%81.drawio.png 2x"
        data-sizes="auto"
        alt="https://cdn.xiaolincoding.com/gh/xiaolincoder/mysql/%E9%94%81/gap%E9%94%81.drawio.png"
        title="img" /></p>
<p>举个具体例子，场景如下：</p>
<p><img
        class="lazyload"
        src="/svg/loading.min.svg"
        data-src="https://cdn.xiaolincoding.com//mysql/other/3af285a8e70f4d4198318057eb955520.png"
        data-srcset="https://cdn.xiaolincoding.com//mysql/other/3af285a8e70f4d4198318057eb955520.png, https://cdn.xiaolincoding.com//mysql/other/3af285a8e70f4d4198318057eb955520.png 1.5x, https://cdn.xiaolincoding.com//mysql/other/3af285a8e70f4d4198318057eb955520.png 2x"
        data-sizes="auto"
        alt="https://cdn.xiaolincoding.com//mysql/other/3af285a8e70f4d4198318057eb955520.png"
        title="img" /></p>
<p>事务 A 执行了这面这条锁定读语句后，就在对表中的记录加上 id 范围为 (2, +∞] 的 next-key lock（next-key lock 是间隙锁+记录锁的组合）。</p>
<p>然后，事务 B 在执行插入语句的时候，判断到插入的位置被事务 A 加了 next-key lock，于是事物 B 会生成一个插入意向锁，同时进入等待状态，直到事务 A 提交了事务。这就避免了由于事务 B 插入新记录而导致事务 A 发生幻读的现象。</p>
<h2 id="httpsxiaolincodingcommysqltransactionphantomhtml幻读被完全解决了吗幻读被完全解决了吗"><a href="https://xiaolincoding.com/mysql/transaction/phantom.html#%e5%b9%bb%e8%af%bb%e8%a2%ab%e5%ae%8c%e5%85%a8%e8%a7%a3%e5%86%b3%e4%ba%86%e5%90%97" target="_blank" rel="noopener noreffer">#</a>幻读被完全解决了吗？</h2>
<p><strong>可重复读隔离级别下虽然很大程度上避免了幻读，但是还是没有能完全解决幻读</strong>。</p>
<p>我举例一个可重复读隔离级别发生幻读现象的场景。</p>
<h3 id="httpsxiaolincodingcommysqltransactionphantomhtml第一个发生幻读现象的场景第一个发生幻读现象的场景"><a href="https://xiaolincoding.com/mysql/transaction/phantom.html#%e7%ac%ac%e4%b8%80%e4%b8%aa%e5%8f%91%e7%94%9f%e5%b9%bb%e8%af%bb%e7%8e%b0%e8%b1%a1%e7%9a%84%e5%9c%ba%e6%99%af" target="_blank" rel="noopener noreffer">#</a>第一个发生幻读现象的场景</h3>
<p>还是以这张表作为例子：</p>
<p><img
        class="lazyload"
        src="/svg/loading.min.svg"
        data-src="https://cdn.xiaolincoding.com//mysql/other/7f9df142b3594daeaaca495abb7133f5-20230309222119359.png"
        data-srcset="https://cdn.xiaolincoding.com//mysql/other/7f9df142b3594daeaaca495abb7133f5-20230309222119359.png, https://cdn.xiaolincoding.com//mysql/other/7f9df142b3594daeaaca495abb7133f5-20230309222119359.png 1.5x, https://cdn.xiaolincoding.com//mysql/other/7f9df142b3594daeaaca495abb7133f5-20230309222119359.png 2x"
        data-sizes="auto"
        alt="https://cdn.xiaolincoding.com//mysql/other/7f9df142b3594daeaaca495abb7133f5-20230309222119359.png"
        title="img" /></p>
<p>事务 A 执行查询 id = 5 的记录，此时表中是没有该记录的，所以查询不出来。</p>
<div class="highlight"><pre tabindex="0" class="chroma"><code class="language-sql" data-lang="sql"><span class="o">#</span><span class="w"> </span><span class="err">事务</span><span class="w"> </span><span class="n">A</span><span class="w">
</span><span class="w"></span><span class="n">mysql</span><span class="o">&gt;</span><span class="w"> </span><span class="k">begin</span><span class="p">;</span><span class="w">
</span><span class="w"></span><span class="n">Query</span><span class="w"> </span><span class="n">OK</span><span class="p">,</span><span class="w"> </span><span class="mi">0</span><span class="w"> </span><span class="k">rows</span><span class="w"> </span><span class="n">affected</span><span class="w"> </span><span class="p">(</span><span class="mi">0</span><span class="p">.</span><span class="mi">00</span><span class="w"> </span><span class="n">sec</span><span class="p">)</span><span class="w">
</span><span class="w">
</span><span class="w"></span><span class="n">mysql</span><span class="o">&gt;</span><span class="w"> </span><span class="k">select</span><span class="w"> </span><span class="o">*</span><span class="w"> </span><span class="k">from</span><span class="w"> </span><span class="n">t_stu</span><span class="w"> </span><span class="k">where</span><span class="w"> </span><span class="n">id</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="mi">5</span><span class="p">;</span><span class="w">
</span><span class="w"></span><span class="n">Empty</span><span class="w"> </span><span class="k">set</span><span class="w"> </span><span class="p">(</span><span class="mi">0</span><span class="p">.</span><span class="mi">01</span><span class="w"> </span><span class="n">sec</span><span class="p">)</span><span class="w">
</span></code></pre></div><p>然后事务 B 插入一条 id = 5 的记录，并且提交了事务。</p>
<div class="highlight"><pre tabindex="0" class="chroma"><code class="language-sql" data-lang="sql"><span class="o">#</span><span class="w"> </span><span class="err">事务</span><span class="w"> </span><span class="n">B</span><span class="w">
</span><span class="w"></span><span class="n">mysql</span><span class="o">&gt;</span><span class="w"> </span><span class="k">begin</span><span class="p">;</span><span class="w">
</span><span class="w"></span><span class="n">Query</span><span class="w"> </span><span class="n">OK</span><span class="p">,</span><span class="w"> </span><span class="mi">0</span><span class="w"> </span><span class="k">rows</span><span class="w"> </span><span class="n">affected</span><span class="w"> </span><span class="p">(</span><span class="mi">0</span><span class="p">.</span><span class="mi">00</span><span class="w"> </span><span class="n">sec</span><span class="p">)</span><span class="w">
</span><span class="w">
</span><span class="w"></span><span class="n">mysql</span><span class="o">&gt;</span><span class="w"> </span><span class="k">insert</span><span class="w"> </span><span class="k">into</span><span class="w"> </span><span class="n">t_stu</span><span class="w"> </span><span class="k">values</span><span class="p">(</span><span class="mi">5</span><span class="p">,</span><span class="w"> </span><span class="s1">&#39;小美&#39;</span><span class="p">,</span><span class="w"> </span><span class="mi">18</span><span class="p">);</span><span class="w">
</span><span class="w"></span><span class="n">Query</span><span class="w"> </span><span class="n">OK</span><span class="p">,</span><span class="w"> </span><span class="mi">1</span><span class="w"> </span><span class="k">row</span><span class="w"> </span><span class="n">affected</span><span class="w"> </span><span class="p">(</span><span class="mi">0</span><span class="p">.</span><span class="mi">00</span><span class="w"> </span><span class="n">sec</span><span class="p">)</span><span class="w">
</span><span class="w">
</span><span class="w"></span><span class="n">mysql</span><span class="o">&gt;</span><span class="w"> </span><span class="k">commit</span><span class="p">;</span><span class="w">
</span><span class="w"></span><span class="n">Query</span><span class="w"> </span><span class="n">OK</span><span class="p">,</span><span class="w"> </span><span class="mi">0</span><span class="w"> </span><span class="k">rows</span><span class="w"> </span><span class="n">affected</span><span class="w"> </span><span class="p">(</span><span class="mi">0</span><span class="p">.</span><span class="mi">00</span><span class="w"> </span><span class="n">sec</span><span class="p">)</span><span class="w">
</span></code></pre></div><p>此时，<strong>事务 A 更新 id = 5 这条记录，对没错，事务 A 看不到 id = 5 这条记录，但是他去更新了这条记录，这场景确实很违和，然后再次查询 id = 5 的记录，事务 A 就能看到事务 B 插入的纪录了，幻读就是发生在这种违和的场景</strong>。</p>
<div class="highlight"><pre tabindex="0" class="chroma"><code class="language-sql" data-lang="sql"><span class="o">#</span><span class="w"> </span><span class="err">事务</span><span class="w"> </span><span class="n">A</span><span class="w">
</span><span class="w"></span><span class="n">mysql</span><span class="o">&gt;</span><span class="w"> </span><span class="k">update</span><span class="w"> </span><span class="n">t_stu</span><span class="w"> </span><span class="k">set</span><span class="w"> </span><span class="n">name</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="s1">&#39;小林coding&#39;</span><span class="w"> </span><span class="k">where</span><span class="w"> </span><span class="n">id</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="mi">5</span><span class="p">;</span><span class="w">
</span><span class="w"></span><span class="n">Query</span><span class="w"> </span><span class="n">OK</span><span class="p">,</span><span class="w"> </span><span class="mi">1</span><span class="w"> </span><span class="k">row</span><span class="w"> </span><span class="n">affected</span><span class="w"> </span><span class="p">(</span><span class="mi">0</span><span class="p">.</span><span class="mi">01</span><span class="w"> </span><span class="n">sec</span><span class="p">)</span><span class="w">
</span><span class="w"></span><span class="k">Rows</span><span class="w"> </span><span class="n">matched</span><span class="p">:</span><span class="w"> </span><span class="mi">1</span><span class="w">  </span><span class="n">Changed</span><span class="p">:</span><span class="w"> </span><span class="mi">1</span><span class="w">  </span><span class="n">Warnings</span><span class="p">:</span><span class="w"> </span><span class="mi">0</span><span class="w">
</span><span class="w">
</span><span class="w"></span><span class="n">mysql</span><span class="o">&gt;</span><span class="w"> </span><span class="k">select</span><span class="w"> </span><span class="o">*</span><span class="w"> </span><span class="k">from</span><span class="w"> </span><span class="n">t_stu</span><span class="w"> </span><span class="k">where</span><span class="w"> </span><span class="n">id</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="mi">5</span><span class="p">;</span><span class="w">
</span><span class="w"></span><span class="o">+</span><span class="c1">----+--------------+------+
</span><span class="c1"></span><span class="o">|</span><span class="w"> </span><span class="n">id</span><span class="w"> </span><span class="o">|</span><span class="w"> </span><span class="n">name</span><span class="w">         </span><span class="o">|</span><span class="w"> </span><span class="n">age</span><span class="w">  </span><span class="o">|</span><span class="w">
</span><span class="w"></span><span class="o">+</span><span class="c1">----+--------------+------+
</span><span class="c1"></span><span class="o">|</span><span class="w">  </span><span class="mi">5</span><span class="w"> </span><span class="o">|</span><span class="w"> </span><span class="err">小林</span><span class="n">coding</span><span class="w">   </span><span class="o">|</span><span class="w">   </span><span class="mi">18</span><span class="w"> </span><span class="o">|</span><span class="w">
</span><span class="w"></span><span class="o">+</span><span class="c1">----+--------------+------+
</span><span class="c1"></span><span class="mi">1</span><span class="w"> </span><span class="k">row</span><span class="w"> </span><span class="k">in</span><span class="w"> </span><span class="k">set</span><span class="w"> </span><span class="p">(</span><span class="mi">0</span><span class="p">.</span><span class="mi">00</span><span class="w"> </span><span class="n">sec</span><span class="p">)</span><span class="w">
</span></code></pre></div><p>整个发生幻读的时序图如下：</p>
<p><img
        class="lazyload"
        src="/svg/loading.min.svg"
        data-src="https://cdn.xiaolincoding.com/gh/xiaolincoder/mysql/%E9%94%81/%E5%B9%BB%E8%AF%BB%E5%8F%91%E7%94%9F.drawio.png"
        data-srcset="https://cdn.xiaolincoding.com/gh/xiaolincoder/mysql/%E9%94%81/%E5%B9%BB%E8%AF%BB%E5%8F%91%E7%94%9F.drawio.png, https://cdn.xiaolincoding.com/gh/xiaolincoder/mysql/%E9%94%81/%E5%B9%BB%E8%AF%BB%E5%8F%91%E7%94%9F.drawio.png 1.5x, https://cdn.xiaolincoding.com/gh/xiaolincoder/mysql/%E9%94%81/%E5%B9%BB%E8%AF%BB%E5%8F%91%E7%94%9F.drawio.png 2x"
        data-sizes="auto"
        alt="https://cdn.xiaolincoding.com/gh/xiaolincoder/mysql/%E9%94%81/%E5%B9%BB%E8%AF%BB%E5%8F%91%E7%94%9F.drawio.png"
        title="img" /></p>
<p>在可重复读隔离级别下，事务 A 第一次执行普通的 select 语句时生成了一个 ReadView，之后事务 B 向表中新插入了一条 id = 5 的记录并提交。接着，事务 A 对 id = 5 这条记录进行了更新操作，在这个时刻，这条新记录的 trx_id 隐藏列的值就变成了事务 A 的事务 id，之后事务 A 再使用普通 select 语句去查询这条记录时就可以看到这条记录了，于是就发生了幻读。</p>
<p>因为这种特殊现象的存在，所以我们认为 <strong>MySQL Innodb 中的 MVCC 并不能完全避免幻读现象</strong>。</p>
<h3 id="httpsxiaolincodingcommysqltransactionphantomhtml第二个发生幻读现象的场景第二个发生幻读现象的场景"><a href="https://xiaolincoding.com/mysql/transaction/phantom.html#%e7%ac%ac%e4%ba%8c%e4%b8%aa%e5%8f%91%e7%94%9f%e5%b9%bb%e8%af%bb%e7%8e%b0%e8%b1%a1%e7%9a%84%e5%9c%ba%e6%99%af" target="_blank" rel="noopener noreffer">#</a>第二个发生幻读现象的场景</h3>
<p>除了上面这一种场景会发生幻读现象之外，还有下面这个场景也会发生幻读现象。</p>
<ul>
<li>T1 时刻：事务 A 先执行「快照读语句」：select * from t_test where id &gt; 100 得到了 3 条记录。</li>
<li>T2 时刻：事务 B 往插入一个 id= 200 的记录并提交；</li>
<li>T3 时刻：事务 A 再执行「当前读语句」 select * from t_test where id &gt; 100 for update 就会得到 4 条记录，此时也发生了幻读现象。</li>
</ul>
<p><strong>要避免这类特殊场景下发生幻读的现象的话，就是尽量在开启事务之后，马上执行 select &hellip; for update 这类当前读的语句</strong>，因为它会对记录加 next-key lock，从而避免其他事务插入一条新记录。</p>
<h2 id="httpsxiaolincodingcommysqltransactionphantomhtml总结总结"><a href="https://xiaolincoding.com/mysql/transaction/phantom.html#%e6%80%bb%e7%bb%93" target="_blank" rel="noopener noreffer">#</a>总结</h2>
<p>MySQL InnoDB 引擎的可重复读隔离级别（默认隔离级），根据不同的查询方式，分别提出了避免幻读的方案：</p>
<ul>
<li>针对<strong>快照读</strong>（普通 select 语句），是通过 MVCC 方式解决了幻读。</li>
<li>针对<strong>当前读</strong>（select &hellip; for update 等语句），是通过 next-key lock（记录锁+间隙锁）方式解决了幻读。</li>
</ul>
<p>我举例了两个发生幻读场景的例子。</p>
<p>第一个例子：对于快照读， MVCC 并不能完全避免幻读现象。因为当事务 A 更新了一条事务 B 插入的记录，那么事务 A 前后两次查询的记录条目就不一样了，所以就发生幻读。</p>
<p>第二个例子：对于当前读，如果事务开启后，并没有执行当前读，而是先快照读，然后这期间如果其他事务插入了一条记录，那么事务后续使用当前读进行查询的时候，就会发现两次查询的记录条目就不一样了，所以就发生幻读。</p>
<p>所以，<strong>MySQL 可重复读隔离级别并没有彻底解决幻读，只是很大程度上避免了幻读现象的发生。</strong></p>
<p>要避免这类特殊场景下发生幻读的现象的话，就是尽量在开启事务之后，马上执行 select &hellip; for update 这类当前读的语句，因为它会对记录加 next-key lock，从而避免其他事务插入一条新记录。</p>
</div><div class="post-footer" id="post-footer">
    <div class="post-info"><div class="post-info-tag"><span><a href="/tags/%E4%BA%8C%E5%8F%AF%E9%87%8D%E5%A4%8D%E8%AF%BB%E9%9A%94%E7%A6%BB%E7%BA%A7%E5%88%AB%E5%AE%8C%E5%85%A8%E8%A7%A3%E5%86%B3%E5%B9%BB%E8%AF%BB%E4%BA%86%E5%90%97/">二、可重复读隔离级别完全解决幻读了吗</a>
                </span></div><div class="post-info-line"><div class="post-info-mod">
                <span>更新于 2023-05-28</span>
            </div><div class="post-info-mod"></div>
        </div><div class="post-info-share">
            <span><a href="javascript:void(0);" title="分享到 Twitter" data-sharer="twitter" data-url="https://acking-you.github.io/posts/%E4%BA%8C%E5%8F%AF%E9%87%8D%E5%A4%8D%E8%AF%BB%E9%9A%94%E7%A6%BB%E7%BA%A7%E5%88%AB%E5%AE%8C%E5%85%A8%E8%A7%A3%E5%86%B3%E5%B9%BB%E8%AF%BB%E4%BA%86%E5%90%97/" data-title="二、可重复读隔离级别完全解决幻读了吗" data-hashtags="二、可重复读隔离级别完全解决幻读了吗"><i class="fab fa-twitter fa-fw"></i></a><a href="javascript:void(0);" title="分享到 Facebook" data-sharer="facebook" data-url="https://acking-you.github.io/posts/%E4%BA%8C%E5%8F%AF%E9%87%8D%E5%A4%8D%E8%AF%BB%E9%9A%94%E7%A6%BB%E7%BA%A7%E5%88%AB%E5%AE%8C%E5%85%A8%E8%A7%A3%E5%86%B3%E5%B9%BB%E8%AF%BB%E4%BA%86%E5%90%97/" data-hashtag="二、可重复读隔离级别完全解决幻读了吗"><i class="fab fa-facebook-square fa-fw"></i></a><a href="javascript:void(0);" title="分享到 WhatsApp" data-sharer="whatsapp" data-url="https://acking-you.github.io/posts/%E4%BA%8C%E5%8F%AF%E9%87%8D%E5%A4%8D%E8%AF%BB%E9%9A%94%E7%A6%BB%E7%BA%A7%E5%88%AB%E5%AE%8C%E5%85%A8%E8%A7%A3%E5%86%B3%E5%B9%BB%E8%AF%BB%E4%BA%86%E5%90%97/" data-title="二、可重复读隔离级别完全解决幻读了吗" data-web><i class="fab fa-whatsapp fa-fw"></i></a><a href="javascript:void(0);" title="分享到 Line" data-sharer="line" data-url="https://acking-you.github.io/posts/%E4%BA%8C%E5%8F%AF%E9%87%8D%E5%A4%8D%E8%AF%BB%E9%9A%94%E7%A6%BB%E7%BA%A7%E5%88%AB%E5%AE%8C%E5%85%A8%E8%A7%A3%E5%86%B3%E5%B9%BB%E8%AF%BB%E4%BA%86%E5%90%97/" data-title="二、可重复读隔离级别完全解决幻读了吗"><i class="fab fa-line fa-fw"></i></a><a href="javascript:void(0);" title="分享到 微博" data-sharer="weibo" data-url="https://acking-you.github.io/posts/%E4%BA%8C%E5%8F%AF%E9%87%8D%E5%A4%8D%E8%AF%BB%E9%9A%94%E7%A6%BB%E7%BA%A7%E5%88%AB%E5%AE%8C%E5%85%A8%E8%A7%A3%E5%86%B3%E5%B9%BB%E8%AF%BB%E4%BA%86%E5%90%97/" data-title="二、可重复读隔离级别完全解决幻读了吗" data-image="https://img-blog.csdnimg.cn/img_convert/7213ded01d686aab797968b2a6064358.png#pic_center"><i class="fab fa-weibo fa-fw"></i></a><a href="javascript:void(0);" title="分享到 Myspace" data-sharer="myspace" data-url="https://acking-you.github.io/posts/%E4%BA%8C%E5%8F%AF%E9%87%8D%E5%A4%8D%E8%AF%BB%E9%9A%94%E7%A6%BB%E7%BA%A7%E5%88%AB%E5%AE%8C%E5%85%A8%E8%A7%A3%E5%86%B3%E5%B9%BB%E8%AF%BB%E4%BA%86%E5%90%97/" data-title="二、可重复读隔离级别完全解决幻读了吗" data-description="二、可重复读隔离级别完全解决幻读了吗"><i data-svg-src="/lib/simple-icons/icons/myspace.min.svg"></i></a><a href="javascript:void(0);" title="分享到 Blogger" data-sharer="blogger" data-url="https://acking-you.github.io/posts/%E4%BA%8C%E5%8F%AF%E9%87%8D%E5%A4%8D%E8%AF%BB%E9%9A%94%E7%A6%BB%E7%BA%A7%E5%88%AB%E5%AE%8C%E5%85%A8%E8%A7%A3%E5%86%B3%E5%B9%BB%E8%AF%BB%E4%BA%86%E5%90%97/" data-title="二、可重复读隔离级别完全解决幻读了吗" data-description="二、可重复读隔离级别完全解决幻读了吗"><i class="fab fa-blogger fa-fw"></i></a><a href="javascript:void(0);" title="分享到 Evernote" data-sharer="evernote" data-url="https://acking-you.github.io/posts/%E4%BA%8C%E5%8F%AF%E9%87%8D%E5%A4%8D%E8%AF%BB%E9%9A%94%E7%A6%BB%E7%BA%A7%E5%88%AB%E5%AE%8C%E5%85%A8%E8%A7%A3%E5%86%B3%E5%B9%BB%E8%AF%BB%E4%BA%86%E5%90%97/" data-title="二、可重复读隔离级别完全解决幻读了吗"><i class="fab fa-evernote fa-fw"></i></a></span>
        </div></div><div class="post-nav"><a href="/posts/%E5%B8%83%E9%9A%86%E8%BF%87%E6%BB%A4%E5%99%A8/" class="prev" rel="prev" title="布隆过滤器"><i class="fas fa-angle-left fa-fw"></i>Previous Post</a>
            <a href="/posts/%E4%B8%80%E5%88%9D%E8%A7%81%E7%B4%A2%E5%BC%95/" class="next" rel="next" title="一、初见索引">Next Post<i class="fas fa-angle-right fa-fw"></i></a></div></div>
</div></article></div>
            </main>
            <footer class="footer"><div class="footer-container"><div class="footer-line">由 <a href="https://gohugo.io/" target="_blank" rel="noopener noreffer" title="Hugo 0.86.0">Hugo</a> 强力驱动 | 主题 - <a href="https://github.com/khusika/FeelIt" target="_blank" rel="noopener noreffer" title="FeelIt 1.0.1"><i class="fas fa-hand-holding-heart fa-fw"></i> FeelIt</a>
        </div><div class="footer-line" itemscope itemtype="http://schema.org/CreativeWork"><i class="far fa-copyright fa-fw"></i><span itemprop="copyrightYear">2023</span><span class="author" itemprop="copyrightHolder">&nbsp;<a href="/"></a></span></div>
</div>
</footer>
        </div>

        <div id="fixed-buttons"><a href="#" id="back-to-top" class="fixed-button" title="回到顶部">
                <i class="fas fa-chevron-up fa-fw"></i>
            </a></div><link rel="stylesheet" href="/lib/fontawesome-free/all.min.css"><link rel="stylesheet" href="/lib/animate/animate.min.css"><link rel="stylesheet" href="/lib/katex/katex.min.css"><link rel="stylesheet" href="/lib/katex/copy-tex.min.css"><script src="/lib/autocomplete/autocomplete.min.js"></script><script src="/lib/lunr/lunr.min.js"></script><script src="/lib/lunr/lunr.stemmer.support.min.js"></script><script src="/lib/lunr/lunr.zh.min.js"></script><script src="/lib/lazysizes/lazysizes.min.js"></script><script src="/lib/clipboard/clipboard.min.js"></script><script src="/lib/sharer/sharer.min.js"></script><script src="/lib/katex/katex.min.js"></script><script src="/lib/katex/auto-render.min.js"></script><script src="/lib/katex/copy-tex.min.js"></script><script src="/lib/katex/mhchem.min.js"></script><script>window.config={"code":{"copyTitle":"复制到剪贴板","maxShownLines":200},"comment":{},"math":{"delimiters":[{"display":true,"left":"$$","right":"$$"},{"display":true,"left":"\\[","right":"\\]"},{"display":false,"left":"$","right":"$"},{"display":false,"left":"\\(","right":"\\)"}],"strict":false},"search":{"highlightTag":"em","lunrIndexURL":"/index.json","lunrLanguageCode":"zh","lunrSegmentitURL":"/lib/lunr/lunr.segmentit.js","maxResultLength":100,"noResultsFound":"没有找到结果","snippetLength":50,"type":"lunr"}};</script><script src="/js/theme.min.js"></script></body></html>
